﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace QD.LopPlat.Repository
{
    public static class DataBaseRepository
    {
        /// <summary>
        /// 登陆名
        /// </summary>
        public static string UserName
        {
            set;
            get;
        }

        /// <summary>
        /// 登陆密码
        /// </summary>
        public static string PassWord
        {
            get;
            set;
        }

        /// <summary>
        /// 登陆地址
        /// </summary>
        public static string SourceAddress
        {
            get;
            set;
        }

        /// <summary>
        /// 登陆数据库名
        /// </summary>
        public static string DataBaseName
        {
            get;
            set;
        }

        /// <summary>
        /// 主数据库名
        /// </summary>
        public static string MasterDataBaseName
        {
            get;
            set;
        }

        /// <summary>
        /// 身份验证方式
        /// </summary>
        public static bool IsWindowsSecurity
        {
            get;
            set;
        }

        /// <summary>
        /// 获取所有数据库
        /// </summary>
        /// <returns></returns>
        public static DataTable GetAllUserDataBase()
        {
            using (SqlConnection conn = SqlConn(false))
            {
                conn.Open();
                string sql = "select name from sys.databases where database_id>4 order by name asc";

                DataSet ds = Query(sql, conn);

                return ds.Tables[0];
            }
        }

        /// <summary>
        /// 获取所有用户表
        /// </summary>
        /// <returns></returns>
        public static DataTable GetAllUserTable(string iswhere)
        {
            using (SqlConnection conn = SqlConn(false))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    StringBuilder sql = new StringBuilder();
                    sql.Append(@"select name,
                                identityname=isnull((select sys.columns.name from sys.columns 
	                                where sys.columns.object_id=sys.objects.object_id 
		                                and sys.columns.is_identity=1),'--') from sys.objects where type='u'");

                    if (iswhere != "")
                        sql.Append(" and ").Append(iswhere);

                    sql.Append(" order by name");
                    cmd.CommandText = sql.ToString();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;

                    DataSet ds = Query(cmd);

                    return ds.Tables[0];
                }
            }
        }

        private static DataSet Query(SqlCommand cmd)
        {
            try
            {
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(cmd);

                da.Fill(ds, "ds");
                return ds;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        private static DataSet Query(string strSql, SqlConnection SqlConn)
        {
            try
            {
                DataSet ds = new DataSet();
                SqlDataAdapter da = new SqlDataAdapter(strSql, SqlConn);

                da.Fill(ds, "ds");

                return ds;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public static SqlConnection SqlConn(bool isMaster)
        {
            string sqlConnectionStr = SqlConnectionStr(isMaster);
            return new SqlConnection(sqlConnectionStr);
        }

        /// <summary>
        /// 数据连接字符串
        /// </summary>
        public static string SqlConnectionStr(bool isMaster)
        {
            if (!IsWindowsSecurity)
            {
                return SqlConnBySqlServer(isMaster);
            }
            else
            {
                return SqlConnByLocal(isMaster);
            }
        }

        /// <summary>
        /// 使用sqlserver身份验证
        /// </summary>
        /// <returns></returns>
        private static string SqlConnBySqlServer(bool isMaseter)
        {
            string databaseName = isMaseter ? MasterDataBaseName : DataBaseName;

            return "Data Source=" + SourceAddress + ";User ID=" + UserName + ";Password=" + PassWord + ";Initial Catalog=" + databaseName + "";

        }

        /// <summary>
        /// 使用windows身份验证
        /// </summary>
        /// <returns></returns>
        private static string SqlConnByLocal(bool isMaseter)
        {
            string databaseName = isMaseter ? MasterDataBaseName : DataBaseName;

            return "Data Source=" + SourceAddress + ";Initial Catalog=" + databaseName + ";Integrated Security=true";
        }

        /// <summary>
        /// 验证sql是否连接成功
        /// </summary>
        /// <returns></returns>
        public static bool IsConnection()
        {
            using (SqlConnection conn = SqlConn(false))
            {
                try
                {
                    conn.Open();
                    conn.Close();
                    return true;
                }
                catch (SqlException)
                {
                    return false;
                }
            }
        }

        /// <summary>
        /// 判断某个表是否存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static string CreateTableIsExistsString(string tableName)
        {
            return "select count(1) from sys.tables where name='" + tableName + "'";
        }

        /// <summary>
        /// 判断某个视是否存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static string CreateViewIsExistsString(string view)
        {
            return "select count(1) from sys.views where name='" + view + "'";
        }

        /// <summary>
        /// 取某个对象的定义
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static string sp_helptextStr(string obj)
        {
            return "sp_helptext " + obj;
        }

        //刷新视图
        public static string CreateRefreshViewString(string tableName)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("sp_refreshview ").Append(tableName);

            return sql.ToString();
        }

        /// <summary>
        /// 主数据库的连接
        /// </summary>
        /// <returns></returns>
        public static SqlConnection SqlConn()
        {
            return new SqlConnection(SqlConnectionStr(true));
        }

        /// <summary>
        /// 返回自定义sql
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet GetCustomSql(string sql)
        {
            string _sql = string.Format(@"
                                declare @begindate datetime
                                declare @enddate datetime
                                set @begindate=getdate()
                                {0}
                                set @enddate=getdate()
                                select @begindate as begindate,@enddate as enddate,datediff(ms,@begindate,@enddate) as diffdate
                                ", sql);

            using (SqlConnection conn = SqlConn())
            {
                conn.Open();
                using (SqlCommand cmd = InitSqlCommand(conn, _sql))
                {
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    return ds;
                }
            }
        }

        /// <summary>
        ///  清空数据
        /// </summary>
        public static void ClearDate(string databasename, string tablename)
        {
            string sql = @"--删除表数据
                            exec('alter table '+'" + tablename + @"' + ' disable trigger all delete from '+'" + tablename + @"' + ' alter table '+'" + tablename + @"' + ' enable trigger all')

                            --有自增栏位
                            if exists(select * from  " + databasename + @".sys.columns
	                            where object_id = object_id('" + tablename + @"')
		                            and is_identity = 1)
	                            begin
	                            --将自增栏位当前标识值置为0
	                            DBCC CHECKIDENT ('" + tablename + @"', RESEED, 1)
	                            end";

            using (SqlConnection conn = SqlConn())
            {
                conn.Open();
                using (SqlCommand cmd = InitSqlCommand(conn, sql))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 获取视图列表
        /// </summary>
        /// <param name="iswhere"></param>
        /// <returns></returns>
        public static DataTable GetViews(string iswhere)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select name,objectproperty(object_id,'IsSchemaBound') as IsChemaBound from sys.views");

            if (iswhere != "")
                sql.Append(" where ").Append(iswhere);

            using (SqlConnection conn = SqlConn())
            {
                conn.Open();
                using (SqlCommand cmd = InitSqlCommand(conn, sql.ToString()))
                {
                    DataSet ds = Query(cmd);
                    return ds.Tables[0];
                }
            }
        }

        /// <summary>
        /// 获取指定视图验证结果
        /// </summary>
        /// <returns></returns>
        public static DataTable GetReFreshView(string viewname)
        {
            string sql = @"
                            declare @error varchar(50)
                            begin try
                            exec sp_refreshview " + viewname + " set @error='视图" + viewname + "验证成功！' end try begin catch set @error='视图" + viewname + "验证失败！\r\n'+error_message() end catch select @error as message";
            using (SqlConnection conn = SqlConn())
            {
                conn.Open();
                using (SqlCommand cmd = InitSqlCommand(conn, sql))
                {
                    DataSet ds = Query(cmd);
                    return ds.Tables[0];
                }
            }
        }

        /// <summary>
        /// 获取所有视图验证结果
        /// </summary>
        /// <returns></returns>
        public static DataTable GetReFreshView()
        {
            string sql = "select name from sys.views";

            using (SqlConnection conn = SqlConn())
            {
                conn.Open();
                using (SqlCommand cmd = InitSqlCommand(conn, sql))
                {
                    DataTable dt = new DataTable();
                    dt.Columns.Add("message", typeof(string));

                    DataSet ds = Query(cmd);

                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        sql = @"
                            declare @error varchar(1000)
                            begin try
                            exec sp_refreshview " + dr["name"].ToString() + " set @error='视图" + dr["name"].ToString() + "验证成功！' end try begin catch set @error='视图" + dr["name"].ToString() + "验证失败\r\n'+error_message() end catch select @error";
                        cmd.CommandText = sql;

                        object result = cmd.ExecuteScalar();
                        dt.Rows.Add(result.ToString());
                    }

                    return dt;
                }
            }
        }

        /// <summary>
        /// 获取所有视图的所有引用对象
        /// </summary>
        /// <returns></returns>
        public static DataTable GetReferencedView()
        {
            using (SqlConnection conn = SqlConn())
            {
                conn.Open();

                string sql = @"select  A.name,A.referencedname,B.type from 
                                 (
                                select distinct B.name,object_name(A.referenced_major_id) as referencedname
                                 from sys.sql_dependencies A 
                                inner join sys.views B 
                                on A.object_id=B.object_id
                                )  A inner join sys.objects B on A.referencedname=B.Name order by A.name asc";

                using (SqlCommand cmd = InitSqlCommand(conn, sql))
                {
                    return Query(cmd).Tables[0];
                }
            }
        }

        /// <summary>
        /// 获取指定视图的所有引用对象
        /// </summary>
        /// <returns></returns>
        public static DataTable GetReferencedView(string viewname)
        {
            using (SqlConnection conn = SqlConn())
            {
                conn.Open();

                string sql = @"
                            select  @viewname as name,
                            name as referencedname,
                            type  
                            from sys.objects 
                            where name in (
                            select distinct object_name(referenced_major_id) as referencedname
                             from sys.sql_dependencies 
                            where object_id=object_id(@viewname)
                            )";
                SqlParameter[] parame = { new SqlParameter("@viewname", SqlDbType.VarChar, 50) };
                parame[0].Value = viewname;

                using (SqlCommand cmd = InitSqlCommand(conn, sql, parame))
                {
                    return Query(cmd).Tables[0];
                }
            }
        }

        /// <summary>
        /// 获取指定结构的列信息
        /// </summary>
        /// <param name="structurename"></param>
        /// <returns></returns>
        public static DataTable GetColumns(string structurename)
        {
            using (SqlConnection conn = SqlConn())
            {
                conn.Open();
                string sql = @"select A.*,B.name as user_type_name from sys.columns A 
	                                        inner join sys.types B on A.user_type_id=B.user_type_id 
                                        where A.object_id=object_id('" + structurename + "')";

                using (SqlCommand cmd = InitSqlCommand(conn, sql))
                {
                    DataSet ds = Query(cmd);

                    return ds.Tables[0];
                }
            }
        }

        /// <summary>
        /// 读取指定表主键
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public static DataTable GetPrimaryKey(string tablename)
        {
            using (SqlConnection conn = SqlConn())
            {
                string sql = @"select t1.name as TableName,
	                                t2.name as PrimaryName,
	                                t4.index_column_id as IndexColumnID,
	                                t5.name as ColumnName,
	                                t6.name as typename
                                from sys.objects t1
                                join sys.objects t2 on t2.parent_object_id = t1.object_id
                                join sys.indexes t3 on t3.object_id = t2.parent_object_id and t3.name = t2.name
                                join sys.index_columns t4 on t4.object_id = t3.object_id and t4.index_id = t3.index_id
                                join sys.columns t5 on t5.object_id = t1.object_id and t5.column_id = t4.column_id 
                                join sys.types t6 on t5.user_type_id=t6.user_type_id
                                where t2.type = 'pk'
	                                and t1.object_id = object_id('" + tablename + "')";

                conn.Open();

                using (SqlCommand cmd = InitSqlCommand(conn, sql))
                {
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    return ds.Tables[0];
                }
            }
        }

        /// <summary>
        /// 读取指定表所有索引
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public static DataTable GetIndexes(string tablename)
        {
            using (SqlConnection conn = SqlConn())
            {
                string sql = @"select A.name,A.type_desc,C.name as ColumnName from sys.indexes A
                                inner join sys.index_columns B on A.object_id=B.object_id and A.index_id=B.index_id 
                                inner join sys.columns C on A.object_id=C.object_id and B.column_id=C.column_id
                                where A.object_id=object_id('" + tablename + "')";

                conn.Open();

                using (SqlCommand cmd = InitSqlCommand(conn, sql))
                {
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    return ds.Tables[0];
                }
            }
        }

        /// <summary>
        /// 获取指定的视图明细
        /// </summary>
        /// <param name="viewname"></param>
        /// <returns></returns>
        public static DataTable GetViewDetail(string viewname)
        {
            using (SqlConnection conn = SqlConn())
            {
                conn.Open();
                string sql = "select * from sys.views where name='" + viewname + "'";
                using (SqlCommand cmd = InitSqlCommand(conn, sql))
                {
                    return Query(cmd).Tables[0];
                }
            }
        }

        /// <summary>
        /// 获取指定表明细
        /// </summary>
        /// <returns></returns>
        public static DataTable GetTableDetail(string tablename)
        {
            using (SqlConnection conn = SqlConn())
            {
                conn.Open();
                string sql = "select * from sys.tables where name='" + tablename + "'";
                using (SqlCommand cmd = InitSqlCommand(conn, sql))
                {
                    return Query(cmd).Tables[0];
                }
            }
        }

        /// <summary>
        /// 备份数据库
        /// </summary>
        /// <param name="databasename">数据库名称</param>
        /// <param name="filepath">备份地址</param>
        /// <param name="istype">1 完整数据库 2仅备份日志</param>
        public static void BackUp(string databasename, string filepath, string backupname, byte istype, bool isFormat)
        {
            StringBuilder sql = new StringBuilder();
            switch (istype)
            {
                case 1:
                    sql.Append("backup database " + databasename + " to disk=N'" + filepath + "' with STATS = 10");
                    break;
                case 2:
                    sql.Append("backup log " + databasename + " to disk=N'" + filepath + "' with STATS = 10");
                    break;
                case 3:
                    sql.Append("backup database " + databasename + " to disk=N'" + filepath + "' with differential, STATS = 10");
                    break;
                default: break;
            }

            sql.Append(", name=N'" + backupname + "'");
            if (isFormat)
                sql.Append(",format");

            using (SqlConnection conn = SqlConn())
            {
                conn.Open();
                using (SqlCommand cmd = InitSqlCommand(conn, sql.ToString()))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /// <summary>
        /// 读取备份记录
        /// </summary>
        /// <param name="iswhere"></param>
        public static DataTable GetBackupList(string database_name)
        {
            string sql = @"select A.backup_set_id,A.database_name,A.user_name,A.server_name,A.machine_name,A.backup_size,
                            A.name,A.backup_start_date,A.backup_finish_date,A.type,A.position,
                            B.physical_device_name,
                            typename=(case A.type when 'D' then '数据库' when 'I' then '差异数据库' when 'L' then '日志' end)  
                         from msdb..backupset A inner join msdb..backupmediafamily B on A.media_set_id=B.media_set_id and A.database_name='" + database_name + "' order by A.backup_set_id asc";

            using (SqlConnection conn = SqlConn())
            {
                conn.Open();
                using (SqlCommand cmd = InitSqlCommand(conn, sql))
                {
                    SqlDataAdapter da = new SqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    return ds.Tables[0];
                }
            }
        }

        /// <summary>
        /// 还原数据库
        /// </summary>
        /// <param name="databasename">数据库名称</param>
        /// <param name="filepath"></param>
        public static void ReStore(string databasename, string filepath)
        {
            try
            {
                string sql = @"use master
                    declare @dbname varchar(50)
                    set @dbname='" + databasename + "'";

                sql = string.Concat(sql, @"declare @sql varchar(50)
                    declare cs_result cursor local for select 'kill '+cast(spid as varchar(50)) from sys.sysprocesses 
	                    where db_name(dbid)=@dbname 

                    open cs_result
                    fetch next from cs_result into @sql
                    while @@fetch_status=0
                    begin
	                    execute(@sql)
	                    fetch next from cs_result into @sql
                    end
                    close cs_result
                    deallocate cs_result
                    ");
                sql = string.Concat(sql, @" restore database " + databasename + " from disk='" + filepath + "' with replace");

                using (SqlConnection conn = SqlConn())
                {
                    conn.Open();
                    using (SqlCommand cmd = InitSqlCommand(conn, sql))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 还原数据库
        /// </summary>
        /// <param name="databasename">数据库名称</param>
        /// <param name="dt">备份集合</param>
        public static void ReStore(string databasename, DataTable dt)
        {
            try
            {
                string sql = @"use master
                    declare @dbname varchar(50)
                    set @dbname='" + databasename + "'";

                sql = string.Concat(sql, @"declare @sql varchar(50)
                    declare cs_result cursor local for select 'kill '+cast(spid as varchar(50)) from sys.sysprocesses 
	                    where db_name(dbid)=@dbname 

                    open cs_result
                    fetch next from cs_result into @sql
                    while @@fetch_status=0
                    begin
	                    execute(@sql)
	                    fetch next from cs_result into @sql
                    end
                    close cs_result
                    deallocate cs_result
                    ");

                foreach (DataRow dr in dt.Rows)
                {
                    if (dr["type"].ToString() == "L")
                        sql = string.Concat(sql, @" restore log " + databasename + " from disk='" + dr["physical_device_name"].ToString() + "' with NOUNLOAD,replace,file=" + dr["position"].ToString());
                    else
                        sql = string.Concat(sql, @" restore database " + databasename + " from disk='" + dr["physical_device_name"].ToString() + "' with NOUNLOAD,replace,file=" + dr["position"].ToString());
                }

                using (SqlConnection conn = SqlConn())
                {
                    conn.Open();
                    using (SqlCommand cmd = InitSqlCommand(conn, sql))
                    {
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        private static SqlCommand InitSqlCommand(SqlConnection con, string sql, params SqlParameter[] parame)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;

            if (parame != null)
            {
                foreach (SqlParameter para in parame)
                {
                    cmd.Parameters.Add(para);
                }
            }

            return cmd;
        }
    }
}
